# -*- coding: utf-8 -*-
'''
Created on 21 mars 2011

@author: bressu

This module is both a datamodel and a dao implementation.
'''
import sqlite3
import os

CONNECTION = None # TODO add a connection pool

def checkStorage(dburl):
    """
    Test if the databse existe if not create a new one
    """
    if os.path.isfile(dburl):
        conn = sqlite3.connect(dburl)
        conn.row_factory = sqlite3.Row
        return conn
    else:
        return initStorage(dburl)

def initStorage(dburl):
    """
    This function should be used to ensure the backend storage is available 
    """
    conn = sqlite3.connect(dburl)
    conn.row_factory = sqlite3.Row
    c = conn.cursor()
    c.executescript("""
        %s;
        %s; 
        %s; 
        %s; 
        %s; 
        %s;
        %s;
        %s;
        %s;
        """ % (Device.create_sql(),
               User.create_sql(),
               Role.create_sql(),
               ContactField.create_sql(),
               Contact.create_sql(),               
               Command.create_sql(),
               Location.create_sql(),
               Status.create_sql(),
               UploadedFile.create_sql())
        )
    conn.commit()
    return conn
    
def getConnection():
    if CONNECTION is None:
        initStorage(None)
    return CONNECTION

def releaseConnection(cnx):    
    pass

    
def with_transaction_required(commit):
    """
    Decorator to handle local transaction.
    If none cnx arguments keywork is passed to the decorated function
    then this decorator create a new connexion.
    if the commit decorator argument is True, then if a local transaction where created
    then commit the local transaction.
    A the end, release the local connextion if any.
    """
    def wrap(f):
        
        def wrapped(*args, **kwargs):
            if kwargs['cnx'] is None:
                cnx  = getConnection()
                kwargs['cnx'] = cnx
                local = True
            else:
                local = False
            try:
                return f(*args, **kwargs)
            finally:
                if local : 
                    if commit :
                        cnx.commit()
                        releaseConnection(cnx)    
        return wrapped     
        
    return wrap

class Device:
    """
    A device
    """
    id = None
    # owner of the device
    owner = None
    
    # api key
    key = None
    
    TABLE = "device"
        
    
    @classmethod
    def create_sql(cls):
        return """
            create table %s (
                id integer PRIMARY KEY ASC,
                owner text,
                key text UNIQUE
            )
            """ % (Device.TABLE,)
            

    @with_transaction_required(True)
    def create(self, cnx=None):
        """
        Make a new instance persistent
        """
        cursor = cnx.cursor()
        sql_base = "INSERT into %s (id, owner, key) values (NULL,?,?)" % (Device.TABLE,)
        cursor.execute(sql_base, (self.owner, self.key))
        self.id = cursor.lastrowid;    
        cursor.close()
        
    
    @classmethod
    @with_transaction_required(False)
    def read(cls, id=None, key=None, cnx=None):
        """
        Retrieve a device
        If id  nor key are supplied, retrieve all device
        This is a generator        
        """
        cursor = cnx.cursor()
        sql_base = "SELECT id, owner, key from device"
        if id and not key:
            cursor.execute(sql_base + " where id = ?", (id,))
        elif not id and key:
            cursor.execute(sql_base + " where key = ?", (key,))
        else:
            cursor.execute(sql_base)
        for id, owner, key in cursor:
            device = Device()
            device.id = id
            device.owner = owner
            device.key = key
            yield device        
        cursor.close()
        
    @with_transaction_required(True)
    def update(self, cnx=None):
        """
        Update existing row. 
        Currently only key can be updated
        """
        cursor = cnx.cursor()
        sql_base = "UPDATE  %s SET  key=? where id=?" % (Device.TABLE,)
        cursor.execute(sql_base, (self.key, self.id))            
        cursor.close()
            
class User:
    """
    User of the app
    """
    login = None
    password = None
    deviceId =  None
    name = None
    admin = 0
       
       
    TABLE = "user"
    
    @classmethod
    def create_sql(cls):
        return """
            create table %s(
                login text PRIMARY KEY,
                password text,
                deviceId integer,
                name TEXT,
                admin integer
                )
            """ % User.TABLE

    @classmethod
    @with_transaction_required(False)
    def read(cls, login=None, cnx=None):
        """
        Retrieve user for a given login.
        If login is not supplied, retrieve all user of the system
        Tiis is a generator        
        """
        cursor = cnx.cursor()
        sql_base = "SELECT login, password, deviceId, name, admin from %s" % User.TABLE
        if login:
            cursor.execute(sql_base + " where login = ?", (login,))
        else:
            cursor.execute(sql_base)
        for login, password, deviceId, name, admin in cursor:
            user = User()
            user.login = login
            user.password = password
            user.deviceId = deviceId
            user.name = name
            user.admin = admin
            yield user        
        cursor.close()


    @classmethod
    @with_transaction_required(False)
    def read_guess(cls, login=None, cnx=None):
        """
        Retrieve all user who were invited by given user which login is in param.
        If login is not supplied, retrieve all guess of the system
        Tiis is a generator        
        """
        cursor = cnx.cursor()
        sql_base = "SELECT u.login, u.password, u.deviceId, u.name, u.admin from %s u, %s d where u.deviceId=d.id and d.owner != u.login" % (User.TABLE, Device.TABLE)
        if login:
            cursor.execute(sql_base + " and d.owner = ?" , (login,))
        else:
            cursor.execute(sql_base)
        for login, password, deviceId, name, admin in cursor:
            user = User()
            user.login = login
            user.password = password
            user.deviceId = deviceId
            user.name = name
            user.admin = admin
            yield user        
        cursor.close()

        
    @with_transaction_required(True)
    def create(self, cnx=None):
        """
        Make a new instance persistent
        """
        cursor = cnx.cursor()
        cursor.execute("select admin from %s where admin = 1" % User.TABLE)
            
        for admin in cursor:                            
            self.admin = 0
            break
        else:
            self.admin = 1            
                 
        cursor.close()
        cursor = cnx.cursor()
        sql_base = "INSERT into %s (login, password, deviceId, name, admin) values (?,?,?,?, %d)" % (User.TABLE,self.admin)
        cursor.execute(sql_base, (self.login, self.password, self.deviceId, self.name))        
        cursor.close()
        
    @with_transaction_required(True)
    def update(self, cnx=None):
        """
        Update existing row.         
        """
        cursor = cnx.cursor()
        sql_base = "UPDATE  %s SET  name=? , password=? where login=?" % (User.TABLE,)
        cursor.execute(sql_base, (self.name, self.password, self.login ))            
        cursor.close()
        
    @with_transaction_required(True)
    def delete(self, cnx=None):
        """
        Delete an user
        """
        cursor = cnx.cursor()
        sql_base = "DELETE from %s where login=?" % (User.TABLE,)
        cursor.execute(sql_base, (self.login, ))                    
        cursor.close()
        
class Role:
    """
    Authorization granted to a given user
    """
    userId = None
    component = None
    access = None
    
    
    @classmethod
    def create_sql(cls):
        return """
            create table role(
                userId integer,
                component text,
                access TEXT
                )
            """

    
class ContactField():
    """
    This is a field of a contact. We use this model to be as flexible as possible.
    """    
    contactId = None
    name = None
    value = None
    
    TABLE = "contact_field"
    @classmethod
    def create_sql(cls):
        return """
            create table %s(               
                contactId integer,
                name TEXT,
                value TEXT,
                CONSTRAINT pkey PRIMARY KEY (contactId,name)
                )
            """ % ContactField.TABLE
            
    @with_transaction_required(True)
    def create(self, cnx=None):
        """
        Make a new instance persistent
        """
        cursor = cnx.cursor()
        sql_base = "INSERT into %s ( contactId, name, value) values (?,?,?)" % (ContactField.TABLE,)
        cursor.execute(sql_base, (self.contactId, self.name, self.value))            
        cursor.close()
        
    @classmethod
    @with_transaction_required(False)
    def read(cls, contactId, name=None, cnx=None):
        """
        Retrieve fields        
        if name is not supplied, retrieve all field for the given contact
        This is a generator        
        """
        
        cursor = cnx.cursor()
        sql_base = "SELECT contactId, name, value from %s" % ContactField.TABLE
        if name is None:
            cursor.execute(sql_base + " where contactId = ?", (contactId,))
        else :
            cursor.execute(sql_base + " where contactId = ? and name = ?", (contactId, name))           
        for contactId, name, value in cursor:
            cf = ContactField()
            cf.contactId = contactId
            cf.name = name
            cf.value = value
            yield cf        
        cursor.close()
        
            
    
    @with_transaction_required(True)
    def update(self, cnx=None):
        """
        Update existing row
        """
        cursor = cnx.cursor()
        sql_base = "UPDATE  %s SET  value=? where contactId=? and name=?" % (ContactField.TABLE,)
        cursor.execute(sql_base, (self.value, self.contactId, self.name))            
        cursor.close()
    
class Contact():
    """
    An entry of a given device
    """
    id = None
    deviceId = None
    uid = None
    
    
    TABLE = "contact"
    @classmethod
    def create_sql(cls):
        return """
            create table %s(
                id integer PRIMARY KEY ASC,
                deviceId integer,
                uid integer,
                CONSTRAINT one_uid_per_device UNIQUE (deviceId,uid)
                )
            """ % Contact.TABLE
            
    @with_transaction_required(True)
    def create(self, cnx=None):
        """
        Make a new instance persistent
        """
        cursor = cnx.cursor()
        sql_base = "INSERT into %s ( id, deviceId, uid) values (NULL,?,?)" % (Contact.TABLE,)
        cursor.execute(sql_base, (self.deviceId, self.uid))    
        self.id = cursor.lastrowid;          
        cursor.close()
        
    @classmethod
    @with_transaction_required(False)
    def read(cls, deviceId, uid=None, cnx=None):
        """
        Retrieve contact        
        If uid is not supplied, retrieve all contact for the given device        
        This is a generator        
        """

        cursor = cnx.cursor()
        sql_base = "SELECT id, deviceId, uid from %s" % Contact.TABLE
        if uid is None :
            cursor.execute(sql_base + " where deviceId = ?", (deviceId,))
        else:
            cursor.execute(sql_base + " where deviceId = ? and uid = ?", (deviceId, uid))
        for id, deviceId, uid in cursor:
            c = Contact()
            c.id = id
            c.deviceId = deviceId
            c.uid = uid
            yield c       
        cursor.close()
             
class Command():
    """
    A command intended to a given device
    """
    deviceId = None
    timestamp = 0  # when the command was posted
    name = None # name of the command
    arguments = None 
    
    TABLE="command"
    
    @classmethod
    def create_sql(cls):
        return """
            create table %s (                
                deviceId integer,
                timestamp real,
                name text,
                arguments text,
                CONSTRAINT pkey PRIMARY KEY (deviceId, timestamp)
                )
                
            """ % Command.TABLE
            
            
    @with_transaction_required(True)
    def create(self, cnx=None):
        """
        Make a new instance persistent
        """
        cursor = cnx.cursor()
        sql_base = "INSERT into %s ( deviceId, name, timestamp, arguments) values (?,?,?,?)" % (Command.TABLE,)
        cursor.execute(sql_base, (self.deviceId, self.name, self.timestamp, self.arguments))                    
        cursor.close()
        
    @classmethod
    @with_transaction_required(False)
    def read(cls, deviceId,  cnx=None):
        """
        Retrieve command for a given device             
        This is a generator        
        """

        cursor = cnx.cursor()
        sql_base = "SELECT deviceId, name, timestamp, arguments from %s where deviceId=?" % (Command.TABLE,)
        cursor.execute(sql_base , (deviceId,))
        for row in cursor:
            c = Command()
            c.deviceId = row["deviceId"]
            c.name = row["name"]
            c.timestamp = row["timestamp"]
            c.arguments = row["arguments"]
            yield c       
        cursor.close()
    
              
    @with_transaction_required(True)
    def delete(self, cnx=None):
        """
        delete this instance from persistence storage
        """
        cursor = cnx.cursor()
        sql_base = "DELETE from %s where deviceId=? and timestamp=?" % (Command.TABLE,)
        cursor.execute(sql_base, (self.deviceId, self.timestamp))                    
        cursor.close()               
            
class UploadedFile():
    """
    A File either uploaded from the device or from the webbrowse
    """
    deviceId = None    
    name = None # name of the file
    to_device = 1 # must this file go the device ? if not the file has been requested bu a browser
    content=None # this must be a buffer
    
    TABLE="uploaded_file"
    
    @classmethod
    def create_sql(cls):
        return """
            create table %s(                
                deviceId integer,                
                name text,
                to_device integer,
                content blob,
                CONSTRAINT pkey PRIMARY KEY (deviceId,name)
                )
            """ % UploadedFile.TABLE            
             
    @with_transaction_required(True)
    def create(self, cnx=None):
        """
        Make a new instance persistent
        """
        cursor = cnx.cursor()
        sql_base = "INSERT into %s ( deviceId, name, to_device, content) values (?,?,?,?)" % (UploadedFile.TABLE,)
        cursor.execute(sql_base, (self.deviceId, self.name, self.to_device, self.content))                    
        cursor.close()
        
    @classmethod
    @with_transaction_required(False)
    def read(cls, deviceId, filename=None, cnx=None):
        """
        Retrieve file                    
        This is a generator        
        """

        cursor = cnx.cursor()
        sql_base = "SELECT deviceId, name, to_device, content from %s"  % (UploadedFile.TABLE,)
        if filename:
            cursor.execute(sql_base + " where deviceId=? and name=?" , (deviceId, filename))
        else:
            cursor.execute(sql_base + " where deviceId=? " , (deviceId, ))
        for row in cursor:
            f = UploadedFile()
            f.deviceId = row["deviceId"]
            f.name = row["name"]
            f.to_device = row["to_device"]
            f.content = row["content"]
            yield f       
        cursor.close()
    
              
    @with_transaction_required(True)
    def delete(self, cnx=None):
        """
        delete this instance from persistence storage
        """
        cursor = cnx.cursor()
        sql_base = "DELETE from %s where deviceId=? and name=?" % (UploadedFile.TABLE,)
        cursor.execute(sql_base, (self.deviceId, self.name))                    
        cursor.close()   
             

class Location():
    """
    Location of a given device
    """    
    deviceId = None
    lat = 0
    long = 0
    fix = 0
    
    TABLE = "location"
    
    @classmethod
    def create_sql(cls):
        return """
            create table %s (
                deviceId integer,
                lat real,
                long real,
                fix real,
                CONSTRAINT pkey PRIMARY KEY (deviceId)
                )
            """ % Location.TABLE 
    
    @with_transaction_required(True)
    def create(self, cnx=None):
        """
        Make a new instance persistent
        """
        cursor = cnx.cursor()
        sql_base = "INSERT into %s ( deviceId, lat, long, fix) values (?,?,?,?)" % (Location.TABLE,)
        cursor.execute(sql_base, (self.deviceId, self.lat, self.long, self.fix))            
        cursor.close()
        
        
    @classmethod
    @with_transaction_required(False)
    def read(cls, deviceId, cnx=None):
        """
        Retrieve location of a device                
        This is a generator        
        """
        
        cursor = cnx.cursor()
        sql_base = "SELECT deviceId, lat, long, fix from %s where deviceId = ?" % Location.TABLE        
        cursor.execute(sql_base , (deviceId,))           
        for row in cursor:
            loc = Location()
            loc.deviceId = row['deviceId']
            loc.lat = row['lat']
            loc.long = row['long']
            loc.fix = row['fix']
            yield loc        
        cursor.close()
            
    @with_transaction_required(True)
    def update(self, cnx=None):
        """
        Update existing row
        """
        cursor = cnx.cursor()
        sql_base = "UPDATE  %s SET  lat=?, long=?, fix=? where deviceId=? " % (Location.TABLE,)
        cursor.execute(sql_base, (self.lat, self.long, self.fix, self.deviceId))  
        cursor.close()
        
class Status():
    """
    Status of a given device
    """
    deviceId = None
    name = None
    value = None
    
    TABLE = "status"
    
    @classmethod
    def create_sql(cls):
        return """
            create table %s (
                deviceId integer,
                name text,
                value text,
                CONSTRAINT pkey PRIMARY KEY (deviceId,name) 
                )
            """ % Status.TABLE
            
    @with_transaction_required(True)
    def create(self, cnx=None):
        """
        Make a new instance persistent
        """
        cursor = cnx.cursor()
        sql_base = "INSERT into %s ( deviceId, name, value) values (?,?,?)" % (Status.TABLE,)
        cursor.execute(sql_base, (self.deviceId, self.name, self.value))            
        cursor.close()
        
        
    @classmethod
    @with_transaction_required(False)
    def read(cls, deviceId, name=None, cnx=None):
        """
        Retrieve fields        
        if name is not supplied, retrieve all field for the given device
        This is a generator        
        """
        
        cursor = cnx.cursor()
        sql_base = "SELECT deviceId, name, value from %s" % Status.TABLE
        if name is None:
            cursor.execute(sql_base + " where deviceId = ?", (deviceId,))
        else :
            cursor.execute(sql_base + " where deviceId = ? and name = ?", (deviceId, name))           
        for contactID, name, value in cursor:
            cf = Status()
            cf.deviceId = deviceId
            cf.name = name
            cf.value = value
            yield cf        
        cursor.close()
            
    @with_transaction_required(True)
    def update(self, cnx=None):
        """
        Update existing row
        """
        cursor = cnx.cursor()
        sql_base = "UPDATE  %s SET  value=? where deviceId=? and name=?" % (Status.TABLE,)
        cursor.execute(sql_base, (self.value, self.deviceId, self.name))  
        cursor.close()
        
class Settings():
    """
    User account settings
    """
    
    user = None # user login
    session_timeout = 5 # session timeout in minute
    
class Message():
    """
    This is a message
    """     
    pass
     